package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import entity.GoodsImg;
import tools.DBTools;

public class GoodsImgDao {

	//添加图片,一个商品对应的几张图片
	public void addGoodsImg(int goodId, List<GoodsImg> imgList) {
		 Connection conn = DBTools.getInstance().getConnection();
		String sql = "insert into TBL_GOOD_IMG(IMG_ID, GOOD_ID, IMG_URL) values(sq_img_id.nextval, ?, ?)";
		String sql2 = "delete from tbl_good_img where GOOD_ID = " +goodId;
		PreparedStatement ps = null;
		PreparedStatement ps2 = null;
		try {
			ps = conn.prepareStatement(sql);
			ps2 = conn.prepareStatement(sql2);
			ps2.executeUpdate();
			
			for(int i=0; i<imgList.size(); i++) {  //循环输入，一次一张图片
				ps.setInt(1, goodId);
				ps.setString(2, imgList.get(i).getImgUrl());
				ps.executeUpdate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(ps!=null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			DBTools.getInstance().closeConnection(conn, ps2, null);
		}
	}
	//根据商品id获取图片
	public List<GoodsImg> getImgByGoodId(int goodId) {
		List<GoodsImg> imgList = new ArrayList<>();
		 Connection conn = DBTools.getInstance().getConnection();
		String sql = "select * from TBL_GOOD_IMG where GOOD_ID = ?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, goodId);
			rs = ps.executeQuery();
			while(rs.next()) {
				GoodsImg goodsImg = new GoodsImg(rs.getInt("IMG_ID"), rs.getInt("GOOD_ID"),
						rs.getString("IMG_URL"), rs.getString("IMG_TYPE"));
				imgList.add(goodsImg);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBTools.getInstance().closeConnection(conn, ps, rs);
		}
		return imgList;
	}
	
	//根据商品id获取一张图片
		public List<GoodsImg> getOneImgByGoodId(int goodId) {
			List<GoodsImg> imgList = new ArrayList<>();
			 Connection conn = DBTools.getInstance().getConnection();
			String sql = "select * from TBL_GOOD_IMG where GOOD_ID = ?";
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1, goodId);
				rs = ps.executeQuery();
				while(rs.next()) {
					GoodsImg goodsImg = new GoodsImg(rs.getInt("IMG_ID"), rs.getInt("GOOD_ID"),
							rs.getString("IMG_URL"), rs.getString("IMG_TYPE"));
					imgList.add(goodsImg);
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				DBTools.getInstance().closeConnection(conn, ps, rs);
			}
			return imgList;
		}
}
